﻿/**
//  UserEntity.cs
//  Marquee Moment
//  Copyright 2011 Systematix Technocrates. All rights reserved.
**/

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using NLog;

namespace MarqueeMoment.Entities
{
    /// <summary>
    /// This is the class to keep all the transaction method from tblUser
    /// </summary>
    public class UserEntity
    {
        private static Logger logger = LogManager.GetCurrentClassLogger();
        #region Fields

        private Int32 userId;
        private String userName;
        private String password;
        private bool userType;

        private List<UserEntity> userList;

        #endregion Fields
        
        #region Constructors

        public UserEntity()
            : base()
        {
            userId = 0;
            userName = String.Empty;
            password = String.Empty;
            userType=false;
            userList = new List<UserEntity>();
        }


        #endregion Constructors

        #region Properties

        public Int32 UserId
        {
            get { return  userId; }
            set { userId = value; }
        }

        public String UserName
        {
            get { return userName; }
            set { userName = value; }
        }

        public String Password
        {
            get { return password; }
            set { password = value; }
        }

        public bool UserType
        {
            get { return userType; }
            set { userType = value; }
        }

        #endregion Properties

        #region Methods

        #region Public
               
        #endregion Public

        #region Private
        
        #endregion Private

        #endregion Methods

        #region Static methods

        #region Public
        /// <summary>
        /// To select UserName and Password from the table
        /// </summary>
        /// <param name="userName">It contains the UserName</param>
        /// <param name="password">It contains the Passord of the User</param>
        /// <returns></returns>
        public static UserEntity Select(string userName, string password)
        {
            UserEntity user = null;
            #region Initialization
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = Connection.GetConnection();
            if (cmd.Connection == null)
                return null;

            cmd.CommandText = "[spSelectUserbyCredential]";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@" + tblUser.UserName, userName);
            cmd.Parameters.AddWithValue("@" + tblUser.Password, password);
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataSet dataSet = new DataSet();
            #endregion Initialization

            try
            {
                adapter.Fill(dataSet);
                if (dataSet.Tables.Count > 0)
                    if (dataSet.Tables[0].Rows.Count > 0)
                    {
                        user = new UserEntity();
                        user.UserId = Convert.ToInt32(dataSet.Tables[0].Rows[0][tblUser.UserId]);
                        user.UserName = Convert.ToString(dataSet.Tables[0].Rows[0][tblUser.UserName]);
                        user.Password = Convert.ToString(dataSet.Tables[0].Rows[0][tblUser.Password]);
                        user.UserType = Convert.ToBoolean(dataSet.Tables[0].Rows[0][tblUser.UserType]);
                    }
            }
            catch (Exception exc)
            {
                logger.Error(exc.StackTrace);
                Dialogs.Error("Error at Select User");
            }
            finally
            {
                if (dataSet != null)
                    dataSet.Dispose();

                Connection.CloseConnection(cmd.Connection);
                cmd.Dispose();
            }
            return user;
        }
        /// <summary>
        /// To select all the records from the tblUser table
        /// </summary>
        /// <returns></returns>  
        public static List<UserEntity> SelectAll()
        {
            List<UserEntity> userList = null;
            UserEntity user = null;
            #region Initialization
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = Connection.GetConnection();
            if (cmd.Connection == null)
                return null;

            cmd.CommandText = "[spSelectAllUser]";
            cmd.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataSet dataSet = new DataSet();
            #endregion Initialization

            try
            {
                adapter.Fill(dataSet);
                if (dataSet.Tables.Count > 0)
                    if (dataSet.Tables[0].Rows.Count > 0)
                    {
                        userList = new List<UserEntity>();
                        for (int count = 0; count < dataSet.Tables[0].Rows.Count; count++)
                        {
                            user = new UserEntity();
                            user.UserId = Convert.ToInt32(dataSet.Tables[0].Rows[count][tblUser.UserId]);
                            user.UserName = Convert.ToString(dataSet.Tables[0].Rows[count][tblUser.UserName]);
                            user.Password = PasswordEncodeDecode.Base64Decode(Convert.ToString(dataSet.Tables[0].Rows[count][tblUser.Password]));
                            user.UserType = Convert.ToBoolean(dataSet.Tables[0].Rows[count][tblUser.UserType]);
                            userList.Add(user);
                        }
                    }
            }
            catch (Exception exc)
            {
                logger.Error(exc.StackTrace);
                Dialogs.Error("Error at Select All Users");
            }
            finally
            {
                if (dataSet != null)
                    dataSet.Dispose();

                Connection.CloseConnection(cmd.Connection);
                cmd.Dispose();
            }
            return userList;
        }
        /// <summary>
        /// To select all the records from tblUser and return Dataset of the records
        /// </summary>
        /// <returns></returns>
        public static DataSet SelectAllUser()
        {
            #region Initialization
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = Connection.GetConnection();
            if (cmd.Connection == null)
                return null;

            cmd.CommandText = "[spSelectAllUser]";
            cmd.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataSet dataSet = new DataSet();
            #endregion Initialization

            try
            {
                adapter.Fill(dataSet);
                if (dataSet.Tables.Count > 0)
                    if (dataSet.Tables[0].Rows.Count > 0)
                    {
                        for (int count = 0; count < dataSet.Tables[0].Rows.Count; count++)
                            dataSet.Tables[0].Rows[count][tblUser.Password] = PasswordEncodeDecode.Base64Decode(Convert.ToString(dataSet.Tables[0].Rows[count][tblUser.Password]));
                        
                        return dataSet;
                    }
            }
            catch (Exception exc)
            {
                logger.Error(exc.StackTrace);
                Dialogs.Error("Error at Select All Users");
            }
            finally
            {
                if (dataSet != null)
                    dataSet.Dispose();

                Connection.CloseConnection(cmd.Connection);
                cmd.Dispose();
            }
            return null;
        }

        /// <summary>
        /// To delete the records of the tbluser table
        /// </summary>
        /// <param name="userId">user Id is the Id to delete the Particular Record /param>
        public static void Delete(int userId)
        {
            //Check for Single Record
            Int32 totalUser = SelectRowCountUser();
            if (totalUser == 1)
            {
                Dialogs.Exclamation("Last user cannot be deleted");
                return;
            }
            #region Initialization
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = Connection.GetConnection();
            if (cmd.Connection == null)
                return;

            cmd.CommandText = "[spDeleteUser]";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@" + tblUser.UserId, userId);
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataSet dataSet = new DataSet();
            #endregion Initialization

            try
            {
                adapter.Fill(dataSet);
            }
            catch (Exception exc)
            {
                logger.Error(exc.StackTrace);
                Dialogs.Error("Error at Delete User/Users");
            }
            finally
            {
                if (dataSet != null)
                    dataSet.Dispose();

                Connection.CloseConnection(cmd.Connection);
                cmd.Dispose();
            }
        }
        /// <summary>
        /// To update the record of the tblUser
        /// </summary>
        /// <param name="user">This contains the user Id to update the record</param>
        public static void Update(UserEntity user)
        {
            //UserEntity user = null;
            #region Initialization
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = Connection.GetConnection();
            if (cmd.Connection == null)
                return;

            cmd.CommandText = "[spUpdateUser]";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@" + tblUser.UserId,user.UserId);
            cmd.Parameters.AddWithValue("@" + tblUser.UserName,user.UserName);
            cmd.Parameters.AddWithValue("@" + tblUser.Password,user.Password);
            cmd.Parameters.AddWithValue("@" + tblUser.UserType,user.UserType);
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataSet dataSet = new DataSet();
            #endregion Initialization

            try
            {
                adapter.Fill(dataSet);
                
            }
            catch (Exception exc)
            {
                logger.Error(exc.StackTrace);
                Dialogs.Error("Error at Update User");
            }
            finally
            {
                if (dataSet != null)
                    dataSet.Dispose();

                Connection.CloseConnection(cmd.Connection);
                cmd.Dispose();
            }
        }
        /// <summary>
        /// To insert the record in tblUser
        /// </summary>
        /// <param name="user">It contains the information to insert the record i.e name of the user,password and type</param>
        public static void Insert(UserEntity user)
        {
            #region Initialization
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = Connection.GetConnection();
            if (cmd.Connection == null)
                return;

            cmd.CommandText = "[spInsertUser]";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@" + tblUser.UserName, user.UserName);
            cmd.Parameters.AddWithValue("@" + tblUser.Password, user.Password);
            cmd.Parameters.AddWithValue("@" + tblUser.UserType, user.UserType);
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataSet dataSet = new DataSet();
            #endregion Initialization

            try
            {
                adapter.Fill(dataSet);

            }
            catch (Exception exc)
            {
                logger.Error(exc.StackTrace);
                Dialogs.Error("Error at Insert User");
            }
            finally
            {
                if (dataSet != null)
                    dataSet.Dispose();

                Connection.CloseConnection(cmd.Connection);
                cmd.Dispose();
            }
        }
        /// <summary>
        /// To count the total number of record in the tblUser
        /// </summary>
        /// <returns></returns>
        public static Int32 SelectRowCountUser()
        {
            #region Initialization
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = Connection.GetConnection();
            if (cmd.Connection == null)
                return 0;

            cmd.CommandText = "[spSelectRowCountUser]";
            cmd.CommandType = CommandType.StoredProcedure;
            #endregion Initialization

            try
            {
                return (int)cmd.ExecuteScalar();
            }

            catch (Exception exc)
            {
                logger.Error(exc.StackTrace);
                Dialogs.Error("Error at Select Row Count of tblUser");
            }
            finally
            {
                Connection.CloseConnection(cmd.Connection);
                cmd.Dispose();
            }
            return 0;
        }


        /// <summary>
        /// To select the User from tblUser table
        /// </summary>
        /// <param name="sqlQuery">It contains the sqlquary to select the records from the table</param>
        /// <returns></returns>
        public static DataSet SelectSQLUser(String sqlQuery)
        {
            #region Initialization
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = Connection.GetConnection();
            if (cmd.Connection == null)
                return null;

            cmd.CommandText = sqlQuery;
            cmd.CommandType = CommandType.Text;

            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataSet dataSet = new DataSet();
            #endregion Initialization

            try
            {
                adapter.Fill(dataSet);

                if (dataSet.Tables.Count > 0)
                    if (dataSet.Tables[0].Rows.Count > 0)
                    {
                        for (int count = 0; count < dataSet.Tables[0].Rows.Count; count++)
                            dataSet.Tables[0].Rows[count][tblUser.Password] = PasswordEncodeDecode.Base64Decode(Convert.ToString(dataSet.Tables[0].Rows[count][tblUser.Password]));

                        return dataSet;
                    }      
            }
            catch (Exception exc)
            {
                logger.Error(exc.StackTrace);
                Dialogs.Error("Error at Select User");
            }
            finally
            {
                Connection.CloseConnection(cmd.Connection);
                cmd.Dispose();
            }
            return null;
        }


        /// <summary>
        /// To not to save the duplicate record in the tblUser table
        /// </summary>
        /// <param name="user">This contains the information of user like UserName and UserId</param>
        /// <returns>true or false</returns>
        public static bool IsExistUser(UserEntity user)
        {
            #region Initialization
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = Connection.GetConnection();
            cmd.CommandText = "[spSelectExistingUser]";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@" + tblUser.UserName, user.UserName.Trim());
            cmd.Parameters.AddWithValue("@" + tblUser.UserId, user.UserId);
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);

            #endregion Initialization

            try
            {
                Int32 existKey = Convert.ToInt32(cmd.ExecuteScalar());
                if (existKey > 0)
                {
                    return true;
                }
            }
            catch (Exception exc)
            {
                logger.Error(exc.StackTrace);
                Dialogs.Error("Error at CheckValidation in User");
            }
            finally
            {
                Connection.CloseConnection(cmd.Connection);
                cmd.Dispose();
            }
            return false;
        }

        
        #endregion Public

        #endregion Static methods
    }
}
